﻿using System;
using MySql.Data.MySqlClient;
using OpusSuite.Utility;
using System.Data.Common;
using OpusSuite.Models.Lib;
using MvcMiniProfiler;

namespace OpusSuite.Models.Providers
{
    class MySqlOpusSuiteProvider : IOpusSuiteProvider
    {
        private readonly DbConnection _connection;
        public DbConnection Connection { get { return _connection; } }

        public string Name { get; set; }

        public string Version { get; set; }

        public MySqlOpusSuiteProvider(string connectionString)
        {
            var cnn = new MySqlConnection(connectionString);

            // wrap the connection with a profiling connection that tracks timings 
            _connection = new MvcMiniProfiler.Data.EFProfiledDbConnection(cnn, MiniProfiler.Current);
            //_connection = cnn;
        }

        public bool TryDbConnection(string userName, string password, string databaseName, string serverName)
        {
            string connectionString = ConnectionString(userName, password, databaseName, serverName);

            try
            {
                using (var connection = new MySqlConnection(connectionString))
                {
                    connection.Open();
                    connection.Close();
                }
            }
            catch (Exception e)
            {
                throw new ModelException("MySQL Server: Unable to connect to database: ", e);
            }

            return true;
        }

        private void TryDBConnection(string sConnection)
        {
            try
            {
                using (var connection = new MySqlConnection(sConnection))
                {
                    connection.Open();
                    connection.Close();
                }
            }
            catch (Exception e)
            {
                throw new ModelException("MySQL Server: Unable to connect to database: ", e);
            }
        }

        public bool SetupCreateDatabase(OpusSuiteModel model, string dbUserName, string dbPassword, string dbDatabase, string dbServer, string userName)
        {
            string s = ConnectionString(dbUserName, dbPassword, dbDatabase, dbServer);
            string sCheck = ConnectionString(dbUserName, dbPassword, "mysql", dbServer);

            // Causes database to be generated using Code First
            TryDBConnection(sCheck);

            try
            {
                using (var connection = new MySqlConnection(sCheck))
                {
                    connection.Open();

                    MySqlCommand c = new MySqlCommand("CREATE DATABASE IF NOT EXISTS " + dbDatabase, connection);
                    c.ExecuteNonQuery();

                    connection.Close();
                }

                using (var connection = new MySqlConnection(s))
                {
                    connection.Open();

                    try
                    {
                        MySqlCommand c = new MySqlCommand("CREATE USER '" + userName + "'@'%' IDENTIFIED BY '" + userName + "'", connection);
                        c.ExecuteNonQuery();

                        c.CommandText = "grant ALL ON *.* to " + userName + " with grant option";
                        c.ExecuteNonQuery();
                    }
// ReSharper disable EmptyGeneralCatchClause
                    catch
// ReSharper restore EmptyGeneralCatchClause
                    {
                        // ignore already exists.  TODO
                    }

                    connection.Close();
                }

                // Make sure Code First creates the database here
                model.Context.CreateDatabase(dbUserName, dbPassword, dbDatabase, dbServer, Enums.ServerTypes.MySqlServer);

                using (var connection = new MySqlConnection(s))
                {
                    connection.Open();

                    MySqlCommand c = new MySqlCommand("grant ALL ON " + dbDatabase + ".* to " + userName + " with grant option", connection);
                    c.ExecuteNonQuery();

                    connection.Close();
                }

                return true;
            }
            catch (Exception e)
            {
                throw new ModelException("MySQL Server: Unable to create database schema: ", e);
            }
        }

        public string ConnectionString(string userName, string password, string databaseName, string serverName)
        {
            return "Server=" + serverName + ";Database=" + databaseName + ";Uid=" + userName + ";Pwd=" +
                   password;
        }

        public bool ChangePassword(string userName, string newPassword)
        {
            try
            {
                _connection.Open();

                var c = _connection.CreateCommand();
                c.CommandText = "SET PASSWORD FOR '" + userName + "'@'%' = PASSWORD('" + newPassword + "')";
                c.ExecuteNonQuery();

                _connection.Close();
            }
            catch (Exception e)
            {
                throw new ModelException("Cannot change password for " + userName + ": ", e);
            }

            return true;
        }
    }
}
